package com.svse.controller;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 前端控制器 easyExcel工具
 * </p>
 *
 * @author 李宇航
 * @since 2019-08-23
 */
@Controller
@RequestMapping("/easyExcel")
public class EasyExcelController {
    private static Logger log = LoggerFactory.getLogger(EasyExcelController.class);

    @RequestMapping("/textExcel")
    public void writeExcelOneSheetOnceWrite(HttpServletResponse response) throws Exception {

        ServletOutputStream out = response.getOutputStream();
        // 设置EXCEL名称
        String fileName = new String(("SystemExcel").getBytes(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);


        // 设置SHEET
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("sheet1");
        // 设置标题
        Table table = new Table(1);
        List<List<String>> titles = new ArrayList<List<String>>();
        titles.add(Arrays.asList("用户ID"));
        titles.add(Arrays.asList("名称"));
        titles.add(Arrays.asList("年龄"));
        titles.add(Arrays.asList("生日"));
        titles.add(Arrays.asList("性别"));
        titles.add(Arrays.asList("爱好"));
        table.setHead(titles);

        SimpleDateFormat smp = new SimpleDateFormat("yyyy-mm-dd hh:mm");
        // 查询数据导出即可 比如说一次性总共查询出1000条数据
        List<List<String>> userList = new ArrayList<>();
        Date beginDate = new Date();
        log.info("开始时间:" + beginDate.toString());
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < 100; i++) {
            userList.add(Arrays.asList("ID_" + i, "小明" + i, "年龄" + String.valueOf(i), "生日" + smp.format(new Date()), "性别男", "爱好羽毛球第" + i + "次"));
        }
        Date endDate = new Date();
        log.info("100W数据录入时间:" + endDate.toString());
        //获取结束时间
        long endTime = System.currentTimeMillis();
        log.info("100W数据录入耗时:" + (endTime - startTime) + "ms");
        writer.write0(userList, sheet, table);
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");

        try {
            //Code
        } catch (Exception e) {
            if (e instanceof NullPointerException) {
                // status=-1
            }
        }
        writer.finish();
        Date stopDate = new Date();
        long time = System.currentTimeMillis(); //获取结束时间
        log.info("100W数据写入时间:" + stopDate.toString());
        log.info("100W数据写入耗时:" + (time - endTime) + "ms");
    }


    @RequestMapping("/textExcel2")
    public void textExcel2(HttpServletResponse response) throws Exception {

        ServletOutputStream  outputStream = response.getOutputStream();


        // 设置SHEET
        // 设置标题
        List<List<String>> titles = new ArrayList<List<String>>();
        titles.add(Arrays.asList("用户ID"));
        titles.add(Arrays.asList("名称"));
        titles.add(Arrays.asList("年龄"));
        titles.add(Arrays.asList("生日"));
        titles.add(Arrays.asList("性别"));
        titles.add(Arrays.asList("爱好"));



        try {
            //Code
        } catch (Exception e) {
            if (e instanceof NullPointerException) {
                // status=-1
            }
        }

    }


}
